﻿using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Windows.Forms;
using Excel = Microsoft.Office.Interop.Excel;
using System.IO;

namespace ExcelChecker
{
    public partial class MainForm : Form
    {
        Excel.Application xlApp1;
        OpenFileDialog openFileDialog1;
        SaveFileDialog saveFileDialog1;

        string filename;

        public MainForm()
        {
            InitializeComponent();
        }

        private void btnBrowse_Click(object sender, EventArgs e)
        {
            openFileDialog1 = new OpenFileDialog();
            openFileDialog1.Filter = "Excel 2003|*.xls|Excel 2007 - 2010|*.xlsx|Excel 2007 - 2010 with Macros|*.xlsm";
            //openFileDialog1.Filter = "Excel 2010 with Macros|*.xlsm";
            openFileDialog1.RestoreDirectory = true;

            if (openFileDialog1.ShowDialog() == DialogResult.OK)
            {
                try
                {
                    if ((filename = openFileDialog1.FileName) != null)
                    {
                        tbFile.Text = filename;
                    }
                }
                catch (Exception ex)
                {
                    MessageBox.Show("Error: Could not read file from disk, please try again!");
                }
            }
        }

        private void btnCheck_Click(object sender, EventArgs e)
        {
            string[] errors = new string[] { "#NULL!", "#DIV/0!", "#VALUE!", "#REF!", "#NAME?", "#NUM!", "#N/A" };
            tbErrorOutput.Text = "Starting...";

            if (filename != null)
            {
                try
                {
                    xlApp1 = new Excel.Application();
                    Excel.Workbook xlWorkbook1 = xlApp1.Workbooks.Open(filename, Type.Missing, true);
                    xlApp1.Calculation = Excel.XlCalculation.xlCalculationManual;
                    foreach (Excel.Worksheet ws1 in xlWorkbook1.Worksheets)
                    {
                        if (tbSheetLike.Text.Length > 0)
                        {
                            if (!ws1.Name.StartsWith(tbSheetLike.Text))
                            {
                                continue;
                            }
                        }

                        System.Threading.Thread.Sleep(1000);
                        tbErrorOutput.AppendText("\r\nChecking sheet: '" + ws1.Name + "'\r\n");

                        Excel.Range xlRange1 = ws1.UsedRange;

                        int rowCount = xlRange1.Rows.Count;
                        int colCount = xlRange1.Columns.Count;

                        for (int i = 1; i <= rowCount; i++)
                        {
                            for (int j = 1; j <= colCount; j++)
                            {
                                string value1 = (xlRange1.Cells[i, j].Text != null) ? xlRange1.Cells[i, j].Text : "";
                                if (value1.StartsWith("#"))
                                {
                                    if (Array.IndexOf(errors, value1) >= 0)
                                    {
                                        string formula1 = ((Object)xlRange1.Cells[i, j].Formula).ToString();
                                        tbErrorOutput.AppendText("Error at: " + i.ToString() + "," + j.ToString() + " => " + value1 + " => " + formula1 + "\r\n");
                                    }
                                }
                            }
                        }
                        //break;
                    }
                    tbErrorOutput.AppendText("\r\n\r\nCompleted.....");
                    xlWorkbook1.Close(false, Type.Missing, Type.Missing);
                    MessageBox.Show("Completed!");
                }
                catch (Exception ex)
                {
                    tbErrorOutput.AppendText(ex.ToString());
                }
            }
            else
            {
                MessageBox.Show("Please select an Excel file!");
            }
        }

        private void btnClose_Click(object sender, EventArgs e)
        {
            if (xlApp1 != null)
            {
                xlApp1 = null;
            }
            this.Close();
        }

        private void btnSave_Click(object sender, EventArgs e)
        {
            try
            {
                saveFileDialog1 = new SaveFileDialog();
                saveFileDialog1.ShowDialog();
                string filename1 = saveFileDialog1.FileName;
                File.WriteAllText(filename1, tbErrorOutput.Text);
                MessageBox.Show("File created successfully!");
            }
            catch (Exception ex)
            {
                MessageBox.Show("Some error occured while writing the file!");
            }
        }

        private void btnCheckRefs_Click(object sender, EventArgs e)
        {
            if (filename != null)
            {
                try
                {
                    if (tbRefs.Text.Length <= 0)
                    {
                        MessageBox.Show("Please enter some term!");
                        return;
                    }
                    tbErrorOutput.Text = "Checking for Ref '" + tbRefs.Text + "'";

                    xlApp1 = new Excel.Application();
                    Excel.Workbook xlWorkbook1 = xlApp1.Workbooks.Open(filename, Type.Missing, true);
                    xlApp1.Calculation = Excel.XlCalculation.xlCalculationManual;
                    foreach (Excel.Worksheet ws1 in xlWorkbook1.Worksheets)
                    {
                        if (tbSheetLike.Text.Length > 0)
                        {
                            if (!ws1.Name.StartsWith(tbSheetLike.Text))
                            {
                                continue;
                            }
                        }
                        System.Threading.Thread.Sleep(1000);
                        tbErrorOutput.AppendText("\r\n\r\nChecking sheet: '" + ws1.Name + "'");

                        Excel.Range xlRange1 = ws1.UsedRange;

                        int rowCount = xlRange1.Rows.Count;
                        int colCount = xlRange1.Columns.Count;

                        for (int i = 1; i <= rowCount; i++)
                        {
                            for (int j = 1; j <= colCount; j++)
                            {
                                string value1 = (xlRange1.Cells[i, j].Text != null) ? xlRange1.Cells[i, j].Text : "";
                                if (value1.Contains(tbRefs.Text))
                                {
                                    string formula1 = ((Object)xlRange1.Cells[i, j].Formula).ToString();
                                    tbErrorOutput.AppendText("\r\nRef found at: " + i.ToString() + "," + j.ToString() + " => " + value1 + " => " + formula1);
                                }
                            }
                        }
                        //break;
                    }
                    tbErrorOutput.AppendText("\r\n\r\nCompleted.....");
                    xlWorkbook1.Close(false, Type.Missing, Type.Missing);
                    MessageBox.Show("Completed!");
                }
                catch (Exception ex)
                {
                    tbErrorOutput.AppendText(ex.ToString());
                }
            }
            else
            {
                MessageBox.Show("Please select an Excel file!");
            }
        }
    }
}